package com.kyivmohyla.kma2.dao;

import com.kyivmohyla.kma2.dao.mapper.HistoryRowMapper;
import com.kyivmohyla.kma2.model.History;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.List;

public class HistoryDaoJdbc extends JdbcDaoSupport implements HistoryDao {

    @Override
    public History getById(int id) throws DataAccessException {
        return getJdbcTemplate()
                .queryForObject(
                        "SELECT history.history_id, history.new_state, history.change_date, history.commentary, history.order_id, history.staff_id, s.name as new_state_name,CONCAT(CONCAT(st.first_name,' '),st.last_name) as staff_name FROM history INNER JOIN STATE s ON s.state_id = history.new_state INNER JOIN staff st ON st.staff_id = history.staff_id WHERE history.history_id = ?",
                        new Object[]{id}, new HistoryRowMapper());
    }

    @Override
    public List<History> getAll() throws DataAccessException {
        return getJdbcTemplate()
                .query("SELECT history.history_id, history.new_state, history.change_date, history.commentary, history.order_id, history.staff_id, s.name as new_state_name,CONCAT(CONCAT(st.first_name,' '),st.last_name) as staff_name FROM history INNER JOIN STATE s ON s.state_id = history.new_state INNER JOIN staff st ON st.staff_id = history.staff_id ORDER BY history.history_id",
                        new HistoryRowMapper());
    }

    @Override
    public void create(History history) throws DataAccessException {
        getJdbcTemplate()
                .update("INSERT INTO history (history.history_id, history.new_state, history.change_date, history.commentary, history.order_id, history.staff_id) VALUES (history_seq.NEXTVAL, ?, ?, ?, ?, ?)",
                        history.getNewState(),
                        history.getChangeDate(),
                        history.getCommentary(),
                        history.getOrderId(), history.getStaffId());
    }

    @Override
    public void update(History history) throws DataAccessException {
        getJdbcTemplate()
                .update("UPDATE history SET history.new_state = ?, history.change_date = ?, history.commentary = ?, history.order_id = ?, history.staff_id = ? WHERE history.history_id = ?",
                        history.getNewState(),
                        history.getChangeDate(),
                        history.getCommentary(),
                        history.getOrderId(), history.getStaffId(),
                        history.getHistoryId());
    }

    @Override
    public void delete(History history) throws DataAccessException {
        getJdbcTemplate().update(
                "DELETE FROM history WHERE history.history_id = ?",
                history.getHistoryId());
    }

    @Override
    public List<History> getHistoryByOrderId(int orderId) throws DataAccessException {
        return getJdbcTemplate()
                .query("SELECT history.history_id, history.new_state, history.change_date, history.commentary, history.order_id, history.staff_id, s.name as new_state_name,CONCAT(CONCAT(st.first_name,' '),st.last_name) as staff_name FROM history INNER JOIN STATE s ON s.state_id = history.new_state INNER JOIN staff st ON st.staff_id = history.staff_id WHERE history.order_id = ? ORDER BY history.history_id",
                        new Object[]{orderId}, new HistoryRowMapper());
    }
}
